//Documentation complete
/**
 * @author Jason Funk
 * @version 2009MR30
 */

package broker;

import problemDomain.ErrorBean;
import problemDomain.ErrorBean.ErrorCode;
import static problemDomain.ErrorBean.throwError;
import static problemDomain.ErrorBean.ErrorCode.*;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * Database broker for the sales pet time period report.
 */
public class SalesPerTimePeriodReportBroker extends ReportBroker {
	
	/* Singleton Constructor */
	
	private static SalesPerTimePeriodReportBroker broker;
	
	/**
	 * Default constructor.
	 * @throws ErrorBean
	 */
	private SalesPerTimePeriodReportBroker () throws ErrorBean {}
	
	/**
	 * Retrieves the static FoodItemBroker instance.
	 * @return static FoodItemBroker
	 * @throws ErrorBean
	 */
	public synchronized static SalesPerTimePeriodReportBroker getBroker () throws ErrorBean {
		if (broker == null)
			broker = new SalesPerTimePeriodReportBroker();
		return broker;
	}
	
	/* END Singleton Constructor */
	
	
	/* Prepared Statements */
	
	private PreparedStatement reportByHourSQL;
	private PreparedStatement getReportByHourSQL () throws ErrorBean {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT name, SUM(price*qty), HOUR(date_placed) FROM menu_item JOIN order_item ON menu_item_id = fk_menu_item_id JOIN `order` ON fk_order_id = order_id WHERE date_placed >= ? AND date_placed <= ? GROUP BY menu_item_id, HOUR(date_placed);");
		return prepare(reportByHourSQL, sb.toString());
	}
	
	private PreparedStatement reportByDaySQL;
	private PreparedStatement getReportByDaySQL () throws ErrorBean {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT name, SUM(price*qty), DAYNAME(date_placed) FROM menu_item JOIN order_item ON menu_item_id = fk_menu_item_id JOIN `order` ON fk_order_id = order_id WHERE date_placed >= ? AND date_placed <= ? GROUP BY menu_item_id, DAYNAME(date_placed);");
		return prepare(reportByDaySQL, sb.toString());
	}
	
	private PreparedStatement reportByWeekSQL;
	private PreparedStatement getReportByWeekSQL () throws ErrorBean {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT name, SUM(price*qty), WEEK(date_placed) FROM menu_item JOIN order_item ON menu_item_id = fk_menu_item_id JOIN `order` ON fk_order_id = order_id WHERE date_placed >= ? AND date_placed <= ? GROUP BY menu_item_id, WEEK(date_placed);");
		return prepare(reportByWeekSQL, sb.toString());
	}
	
	private PreparedStatement reportByMonthSQL;
	private PreparedStatement getReportByMonthSQL () throws ErrorBean {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT name, SUM(price*qty), MONTHNAME(date_placed) FROM menu_item JOIN order_item ON menu_item_id = fk_menu_item_id JOIN `order` ON fk_order_id = order_id WHERE date_placed >= ? AND date_placed <= ? GROUP BY menu_item_id, MONTHNAME(date_placed);");
		return prepare(reportByMonthSQL, sb.toString());
	}
	
	private PreparedStatement reportByYearSQL;
	private PreparedStatement getReportByYearSQL () throws ErrorBean {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT name, SUM(price*qty), YEAR(date_placed) FROM menu_item JOIN order_item ON menu_item_id = fk_menu_item_id JOIN `order` ON fk_order_id = order_id WHERE date_placed >= ? AND date_placed <= ? GROUP BY menu_item_id, Year(date_placed);");
		return prepare(reportByYearSQL, sb.toString());
	}
	
	private PreparedStatement reportTotalSQL;
	private PreparedStatement getReportTotalSQL () throws ErrorBean {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT SUM(price*qty) FROM menu_item JOIN order_item ON menu_item_id = fk_menu_item_id JOIN `order` ON fk_order_id = order_id WHERE date_placed >= ? AND date_placed <= ?;");
		return prepare(reportTotalSQL, sb.toString());
	}
	
	private PreparedStatement reportByDateSQL;
	private PreparedStatement getReportByDateSQL () throws ErrorBean {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT name, SUM(price*qty), DATE(date_placed) FROM menu_item JOIN order_item ON menu_item_id = fk_menu_item_id JOIN `order` ON fk_order_id = order_id WHERE date_placed >= ? AND date_placed <= ? GROUP BY menu_item_id, DATE(date_placed);");
		return prepare(reportByDateSQL, sb.toString());
	}

	/* END Prepare Statements */
	
	
	/* (Broker) Methods */
	
	/**
	 * Generates the report.
	 * @param fromDate the date to lower bound the report
	 * @param toDate the date to upper bound the report
	 * @param timePeriod the time period to report on
	 * @return a <code>List</code> of the results
	 * @throws ErrorBean if the input data are not correct
	 */
	@SuppressWarnings("null")
	public List<String> getSalesPerTimePeriodReport(Date fromDate, Date toDate, String timePeriod) throws ErrorBean{
		List<String> report = new ArrayList<String>();
		
		if (timePeriod == null)
			throwError(
					REPORT_INVALID_DATE,
					timePeriod,
					this);
		
		if(timePeriod.equals("Hour")) {
			PreparedStatement reportByHourSQL = getReportByHourSQL();
			try {
				reportByHourSQL.setDate(1, new java.sql.Date(fromDate.getTime()));
				reportByHourSQL.setDate(2, new java.sql.Date(toDate.getTime()));
				ResultSet results = reportByHourSQL.executeQuery();
				
				while (results.next()) {
					try {
						report.add("<menuitem><name>"+results.getString(1)+"</name><total>"+results.getString(2)+"</total><period>"+results.getString(3)+":00</period></menuitem>");					} catch (SQLException e) {
						throwError(
								ErrorCode.BROKER_SELECT_ERROR,
								e.getMessage(),
								this);
							return null;
					}
				}
				
				return report;
			} catch (SQLException e) {
				throwError(
					ErrorCode.BROKER_PREPARED_STATEMENTS_NOT_AVAILABLE,
					e.getMessage(),
					this);
				return null;
			}
		}
		else if(timePeriod.equals("Day")) {
			PreparedStatement reportByDaySQL = getReportByDaySQL();
			try {
				reportByDaySQL.setDate(1, new java.sql.Date(fromDate.getTime()));
				reportByDaySQL.setDate(2, new java.sql.Date(toDate.getTime()));
				ResultSet results = reportByDaySQL.executeQuery();
				
				while (results.next()) {
					try {
						report.add("<menuitem><name>"+results.getString(1)+"</name><total>"+results.getString(2)+"</total><period>"+results.getString(3)+"</period></menuitem>");					} catch (SQLException e) {
						throwError(
								ErrorCode.BROKER_SELECT_ERROR,
								e.getMessage(),
								this);
							return null;
					}
				}
				
				return report;
			} catch (SQLException e) {
				throwError(
					ErrorCode.BROKER_PREPARED_STATEMENTS_NOT_AVAILABLE,
					e.getMessage(),
					this);
				return null;
			}
		}
		else if(timePeriod.equals("Week")) {
			PreparedStatement reportByWeekSQL = getReportByWeekSQL();
			try {
				reportByWeekSQL.setDate(1, new java.sql.Date(fromDate.getTime()));
				reportByWeekSQL.setDate(2, new java.sql.Date(toDate.getTime()));
				ResultSet results = reportByWeekSQL.executeQuery();
				
				while (results.next()) {
					try {
						report.add("<menuitem><name>"+results.getString(1)+"</name><total>"+results.getString(2)+"</total><period>"+results.getString(3)+"/52</period></menuitem>");					} catch (SQLException e) {
						throwError(
								ErrorCode.BROKER_SELECT_ERROR,
								e.getMessage(),
								this);
							return null;
					}
				}
				
				return report;
			} catch (SQLException e) {
				throwError(
					ErrorCode.BROKER_PREPARED_STATEMENTS_NOT_AVAILABLE,
					e.getMessage(),
					this);
				return null;
			}
		}
		else if(timePeriod.equals("Month")) {
			PreparedStatement reportByMonthSQL = getReportByMonthSQL();
			try {
				reportByMonthSQL.setDate(1, new java.sql.Date(fromDate.getTime()));
				reportByMonthSQL.setDate(2, new java.sql.Date(toDate.getTime()));
				ResultSet results = reportByMonthSQL.executeQuery();
				
				while (results.next()) {
					try {
						report.add("<menuitem><name>"+results.getString(1)+"</name><total>"+results.getString(2)+"</total><period>"+results.getString(3)+"</period></menuitem>");					} catch (SQLException e) {
						throwError(
								ErrorCode.BROKER_SELECT_ERROR,
								e.getMessage(),
								this);
							return null;
					}
				}
				
				return report;
			} catch (SQLException e) {
				throwError(
					ErrorCode.BROKER_PREPARED_STATEMENTS_NOT_AVAILABLE,
					e.getMessage(),
					this);
				return null;
			}
		}
		else if(timePeriod.equals("Year")) {
			PreparedStatement reportByYearSQL = getReportByYearSQL();
			try {
				reportByYearSQL.setDate(1, new java.sql.Date(fromDate.getTime()));
				reportByYearSQL.setDate(2, new java.sql.Date(toDate.getTime()));
				ResultSet results = reportByYearSQL.executeQuery();
				
				while (results.next()) {
					try {
						report.add("<menuitem><name>"+results.getString(1)+"</name><total>"+results.getString(2)+"</total><period>"+results.getString(3)+"</period></menuitem>");
					} catch (SQLException e) {
						throwError(
								ErrorCode.BROKER_SELECT_ERROR,
								e.getMessage(),
								this);
							return null;
					}
				}
				
				return report;
			} catch (SQLException e) {
				throwError(
					ErrorCode.BROKER_PREPARED_STATEMENTS_NOT_AVAILABLE,
					e.getMessage(),
					this);
				return null;
			}
		}
		else if(timePeriod.equals("Date")) {
			PreparedStatement reportByDateSQL = getReportByDateSQL();
			try {
				reportByDateSQL.setDate(1, new java.sql.Date(fromDate.getTime()));
				reportByDateSQL.setDate(2, new java.sql.Date(toDate.getTime()));
				ResultSet results = reportByDateSQL.executeQuery();
				
				while (results.next()) {
					try {
						report.add("<menuitem><name>"+results.getString(1)+"</name><total>"+results.getString(2)+"</total><period>"+results.getString(3)+"</period></menuitem>");
					} catch (SQLException e) {
						throwError(
								ErrorCode.BROKER_SELECT_ERROR,
								e.getMessage(),
								this);
							return null;
					}
				}
				
				return report;
			} catch (SQLException e) {
				throwError(
					ErrorCode.BROKER_PREPARED_STATEMENTS_NOT_AVAILABLE,
					e.getMessage(),
					this);
				return null;
			}
		}
		else
			throwError(
					REPORT_INVALID_DATE,
					timePeriod,
					this);
		return null;
	}
	
	/**
	 * Returns the total value that is used in the report calculation.
	 * @param fromDate the date to lower bound the report
	 * @param toDate the date to upper bound the report
	 * @return the total value
	 * @throws ErrorBean if the input data are not correct
	 */
	public String getTotalReport (Date fromDate, Date toDate) throws ErrorBean {
		String total = new String();
		PreparedStatement reportTotalSQL = getReportTotalSQL();
		try {
			reportTotalSQL.setDate(1, new java.sql.Date(fromDate.getTime()));
			reportTotalSQL.setDate(2, new java.sql.Date(toDate.getTime()));
			ResultSet results = reportTotalSQL.executeQuery();
			
			while (results.next()) {
				try {
					total = new String(results.getString(1));
				} catch (SQLException e) {
					throwError(
							ErrorCode.BROKER_SELECT_ERROR,
							e.getMessage(),
							this);
						return null;
				}
			}
			
			return total;
		} catch (SQLException e) {
			throwError(
				ErrorCode.BROKER_PREPARED_STATEMENTS_NOT_AVAILABLE,
				e.getMessage(),
				this);
			return null;
		}
	}
	
	/* END (Broker) Methods */
	
}
